package com.glsc.ngateway.common.base.repo.mysql.gateway.other;


import com.glsc.ngateway.common.base.domain.mysql.gateway.other.DigitalKey;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface DigitalKeyRepository extends JpaRepository<DigitalKey, Integer>, JpaSpecificationExecutor<DigitalKey> {
    DigitalKey findByKeyIdAndKeyDeleted(Integer keyId,String keyDeleted);

    DigitalKey findByKeyNoAndKeyDeleted(String keyNo,String keyDeleted);

    /**
     * 数字证书在到期日前，仅在用的予以提醒，触发oa流程
     *
     * @param beforeRemindDays 提前提醒天数（正整数）
     * @return
     */
    @Query(value = "select * from t_am_digital_key where date_format(now(),'%Y-%m-%d') >= date_sub(key_expire_date,interval :beforeRemindDays day) and has_remind = 'N' and key_status = '1' and key_deleted = 'N'", nativeQuery = true)
    List<DigitalKey> findAllBeforeExpireDays(@Param("beforeRemindDays") Integer beforeRemindDays);

//    /**
//     * 使用部门是营业部和分公司的，在到期日前
//     * @param keyDeleted
//     * @return
//     */
//    @Query(value = "select * from t_am_digital_key where date_format(now(),'%Y-%m-%d') >= date_sub(key_expire_date,interval 7 day) and (use_department like '%营业部%' or use_department like '%分公司%') and has_remind = 'N' and key_deleted = :keyDeleted", nativeQuery = true)
//    List<DigitalKey> findAllNotHeadDepartmentByKeyDeleted(@Param("keyDeleted") String keyDeleted);

//    List<DigitalKey> findAllByKeyNoInAndKeyDeleted(List<String> keyNoList,String keyDeleted);

    /**
     * 发证机构+序列号作为联合主键（非唯一，因为可能同一个证书在多个分支机构使用，业务习惯多条记录）
     *
     * @param branchAndKeyNoList keyIssueBranch+KeyNo的字符串列表
     * @return
     */
    @Query(value = "select * from t_am_digital_key where CONCAT( ifnull(key_issue_branch,''),'_',ifnull(key_no ,'')) in(:branchAndKeyNoList) and key_deleted = 'N'", nativeQuery = true)
    List<DigitalKey> findByBranchAndKeyNoList(@Param("branchAndKeyNoList") List<String> branchAndKeyNoList);

    List<DigitalKey> findAllByKeyDeletedAndKeyStatus(@Param("keyDeleted")String keyDeleted, @Param("keyStatus")String keyStatus);

    List<DigitalKey> findAllByKeyDeleted(@Param("keyDeleted")String keyDeleted);
}